﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace ManagingBakery
{
    public static class DBHelper
    {
        private static string mainCon = ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString;
        //private static string mainCSCCon = ConfigurationManager.ConnectionStrings["MainConnectionCSCString"].ConnectionString;
        private static string mainSangareddyCon = ConfigurationManager.ConnectionStrings["MainConnectionSangareddyString"].ConnectionString;
        private static string financialCon = ConfigurationManager.ConnectionStrings["FinancialConnectionString"].ConnectionString;

        private static string chandanagarCon = string.Empty;
        private static string kukatpallyCon = string.Empty;
        private static string ameerpetCon = string.Empty;
        private static string miyapurCon = string.Empty;
        private static string jntuCon = string.Empty;

        private static string csc1Con = string.Empty;
        private static string csc2Con = string.Empty;
        private static string csc3Con = string.Empty;

        private static string sangareddyCon = string.Empty;

        internal static DataTable GetBranches()
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT BranchID, BranchName FROM MasterBranches", con);
                adapter.Fill(ds, "Branches");
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetDepartments(string branch)
        {
            try
            {
                SqlConnection con = null;

                con = new SqlConnection(mainCon);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT DeptID, DeptCode + ' - ' + DeptName As Department FROM Department"), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            return null;
        }

        internal static DataTable GetStockToUpdate()
        {
            try
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ItemCode AS Code, ItemName AS Name, AcceptDecimalStock, '' AS Qty FROM MasterItems", mainCon))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch (Exception)
            { }
            return null;
        }

        internal static DataTable GetStockByBranchToUpdate(string branch, string item)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT ItemCode AS Code, ItemName, AcceptDecimalStock, RI.StockAvailable, '' AS StockAdding FROM MasterItems MI JOIN Stock RI ON MI.StockID = RI.StockID WHERE ItemCode like '{0}%' OR ItemName like '{0}%' ORDER BY CONVERT(BIGINT, ItemCode)", item), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int UpdateStockByBranch(DataTable dt, string branch)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;
            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                trans = con.BeginTransaction();

                SqlCommand cmd = new SqlCommand();

                foreach (DataRow row in dt.Rows)
                {
                    if (row[4].ToString() != string.Empty && row[4].ToString() != "0")
                    {
                        cmd.CommandText = string.Format("exec AddStockByItemCode {0}, {1}, 'Manager'", row[0].ToString(), row[4].ToString());
                        cmd.Connection = con;
                        cmd.Transaction = trans;
                        cmd.ExecuteScalar();
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                if (trans != null)
                    trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        internal static int UpdateStockAtCentral(DataTable dt, string branch)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;

            try
            {
                con = new SqlConnection(mainCon);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                trans = con.BeginTransaction();

                SqlCommand cmd = new SqlCommand();

                int ret = -2;

                foreach (DataRow row in dt.Rows)
                {
                    if (row[3].ToString() != string.Empty)
                    {
                        cmd.CommandText = string.Format("INSERT INTO StockUpdateFromManaging (ItemCode, Stock, Branch) VALUES ('{0}', '{1}', '{2}')", row[0].ToString(), row[3].ToString(), branch);
                        cmd.Connection = con;
                        cmd.Transaction = trans;
                        cmd.ExecuteScalar();
                        ret = 0;
                    }
                }
                trans.Commit();
                return ret;
            }
            catch (Exception)
            {
                return -1;
            }
        }

        internal static DataTable GetItemsByBranch(string branch, string item)
        {
            try
            {
                SqlConnection con = null;

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0")
                    con = new SqlConnection(mainCon);
                //else if (branch == "-1")
                //    con = new SqlConnection(mainCSCCon);
                else if (branch == "-2")
                    con = new SqlConnection(mainSangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT MI.ItemCode AS Code, MI.ItemName, MI.Dept, D.DeptName, MI.CostPrice, MI.SellingPrice, MI.AcceptNegativeStock, MI.AcceptDecimalStock, MI.PopupLimit, MI.IsLessStockShown, MI.GroupID, MI.IsMfgVisible, MI.MfgSection, MI.IsPerishable FROM MasterItems MI WITH (NOLOCK) JOIN Department D WITH (NOLOCK) ON MI.Dept = D.DeptID WHERE MI.ItemCode like '{0}%' OR MI.ItemName like '{0}%'", item), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            return null;
        }

        internal static int UpdateItem(string branch, string itemCode, string itemName, int department, double price, bool negativeStock, bool allowDecimal, double popupLimit, bool showInBilling, int group, bool isMfgVisible, string section, bool perishable)
        {
            SqlConnection con = null;

            if (branch == "1")
                con = new SqlConnection(chandanagarCon);
            else if (branch == "2")
                con = new SqlConnection(miyapurCon);
            else if (branch == "3")
                con = new SqlConnection(kukatpallyCon);
            else if (branch == "4")
                con = new SqlConnection(ameerpetCon);
            else if (branch == "5")
                con = new SqlConnection(jntuCon);
            //else if (branch == "6")
            //    con = new SqlConnection(csc1Con);
            //else if (branch == "7")
            //    con = new SqlConnection(csc2Con);
            //else if (branch == "8")
            //    con = new SqlConnection(csc3Con);
            else if (branch == "6")
                con = new SqlConnection(sangareddyCon);
            else if (branch == "0")
                con = new SqlConnection(mainCon);
            //else if (branch == "-1")
            //    con = new SqlConnection(mainCSCCon);
            else if (branch == "-2")
                con = new SqlConnection(mainSangareddyCon);

            if (con.State == ConnectionState.Closed)
                con.Open();

            SqlTransaction trans = con.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddOrModifyItem '{0}', '{1}', {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}", itemCode, itemName, price, department, negativeStock, allowDecimal, popupLimit, showInBilling, group, isMfgVisible, section, perishable), con, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
            }
        }

        internal static DataTable GetAmountsByBranch(DateTime date, string branch, bool branchConnect)
        {
            SqlConnection con = null;

            try
            {
                if (branchConnect)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCon);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCon);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCon);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCon);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    else if (branch == "6")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT A.ID, A.SodexhoAmount AS Sodexho, A.CreditCardAmount AS CreditCard, A.Cashier FROM Amounts A WHERE DateDiff(dd, A.Date, '{0}') = 0 AND A.Branch = {1}", date.ToString("MM/dd/yyyy"), branch), con))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }

                //using (SqlCommand cmd = new SqlCommand(string.Format("SELECT A.ID, A.SodexhoAmount AS Sodexho, A.CreditCardAmount AS CreditCard, A.Cashier FROM Amounts A WHERE DateDiff(dd, A.Date, '{0}') = 0 AND A.Branch = {1}", date.ToString("MM/dd/yyyy"), branch), con))
                //{
                //    cmd.CommandType = CommandType.Text;
                //    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                //    {
                //        DataTable ds = new DataTable();
                //        adapter.Fill(ds);
                //        if (ds != null && ds.Rows.Count > 0)
                //            return ds;
                //    }
                //}
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int GetItemWhetherExists(string branch, string itemCode, string itemName)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0")
                    con = new SqlConnection(mainCon);
                //else if (branch == "-1")
                //    con = new SqlConnection(mainCSCCon);
                else if (branch == "-2")
                    con = new SqlConnection(mainSangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CheckItemExistance {0}, '{1}'", itemCode, itemName), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int UpdateAmounts(string id, string sodexhoAmt, string creditCardAmt, DateTime dtDateSelected, string branch, bool isBranchSelected, string cashier)
        {
            SqlConnection con = null;

            try
            {
                if (isBranchSelected)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCon);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCon);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCon);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCon);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    else if (branch == "6")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                int result = 0;

                //for (int cnt = 1; cnt < 3; cnt++)
                //{
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = string.Format("exec AddAmount '{0}', '{1}', '{2}', '{3}', '{4}', '{5}'", id, dtDateSelected.ToString("MM/dd/yyyy"), sodexhoAmt.ToString(), creditCardAmt.ToString(), branch, cashier);
                    cmd.CommandType = CommandType.Text;
                    result += cmd.ExecuteNonQuery();
                }
                //}
                return result;
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetExpensesByBranch(DateTime dtDateSelected, string branch, bool isBranchSelected)
        {
            SqlConnection con = null;

            try
            {
                if (isBranchSelected)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCon);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCon);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCon);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCon);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    else if (branch == "6")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT E.ID, E.Expense, ME.ExpenseItem, E.Amount, E.Cashier, E.Description FROM Expense E JOIN MasterExpenses ME ON E.Expense = ME.ID WHERE DateDiff(dd, E.Date, '{0}') = 0 AND E.Branch = {1} ORDER BY ID", dtDateSelected.ToString("MM/dd/yyyy"), branch), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static DataTable GetExpenseItemsByBranch(string branch, bool isBranchSelected)
        {
            SqlConnection con = null;

            try
            {
                if (isBranchSelected)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCon);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCon);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCon);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCon);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    else if (branch == "6")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, ExpenseItem FROM MasterExpenses", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int UpdateExpense(string rowID, string expense, string amount, string description, string branch, bool isBranchSelected, DateTime date, string cashier)
        {
            SqlConnection con = null;

            try
            {
                if (isBranchSelected)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCon);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCon);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCon);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCon);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    else if (branch == "6")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                int result = 0;

                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = string.Format("exec ExpensesUpdate {0}, {1}, {2}, '{3}', {4}, '{5}', '{6}'", rowID, expense, amount, description, branch, date.ToString("MM/dd/yyyy"), cashier);
                    cmd.CommandType = CommandType.Text;
                    result = cmd.ExecuteNonQuery();
                }
                return result;
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetBillsByBranch(DateTime dtDateSelected, string branch, bool isBranchSelected)
        {
            SqlConnection con = null;

            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == dtDateSelected.Month && DateTime.Now.Year == dtDateSelected.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", dtDateSelected.ToString("MMM"), dtDateSelected.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", dtDateSelected.ToString("MMM"), dtDateSelected.Year.ToString());
                }

                if (isBranchSelected)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Rs Disc', TotalAmount, TotalNetAmount AS 'Net Amt', BillDate, Branch FROM {2} WHERE IsSpecialBill = 0 AND IsBillCancelled = 0 AND DateDiff(dd, BillDate, '{0}') = 0 AND Branch = {1} ORDER BY ID", dtDateSelected.ToString("MM/dd/yyyy"), branch, BILLTABLE), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        if (ds != null)
                            return ds;
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static DataTable GetBillDetails(DateTime dtDateSelected, string billNo, string branch)
        {
            SqlConnection con = null;

            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == dtDateSelected.Month && DateTime.Now.Year == dtDateSelected.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", dtDateSelected.ToString("MMM"), dtDateSelected.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", dtDateSelected.ToString("MMM"), dtDateSelected.Year.ToString());
                }

                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT BD.ID, BD.Item, MI.ItemName, BD.Qty FROM {3} BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE BillNo = '{0}' AND Branch = {1} AND DATEDIFF(dd, [Date], '{2}') = 0", billNo, branch, dtDateSelected.ToString("MM/dd/yyyy"), BILLDETAILTABLE), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int CancelBill(string billNo, string reason, string branch, bool isBranchSelected)
        {
            SqlConnection con = null;

            try
            {
                if (isBranchSelected)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                int result = 0;

                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = string.Format("exec CancelBillFromManager '{0}', '{1}'", billNo, reason);
                    cmd.CommandType = CommandType.Text;
                    result = cmd.ExecuteNonQuery();
                }
                return result;
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetCakesByBranch(DateTime dtDateSelected, string branch, bool isBranchSelected)
        {
            SqlConnection con = null;

            try
            {
                if (isBranchSelected)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT CakeNo, CONVERT(VARCHAR, BookedDate, 106) AS BookedDate, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, DeliveryTime AS Time, C.Qty, C.Advance, C.Balance, CS.CakeShape, CF.Flavour, C.AlbumModelDesc, C.[Desc] FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE (DateDiff(dd, BookedDate, '{0}') = 0 OR DateDiff(dd, DeliveryDate, '{0}') = 0) AND Branch = {1} AND IsCancelled = 0 ORDER BY C.ID", dtDateSelected.ToString("MM/dd/yyyy"), branch), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int CancelCakeBill(string cakeBillNo, string branch, bool isBranchSelected)
        {
            SqlConnection con = null;

            try
            {
                if (isBranchSelected)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                int result = 0;

                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = string.Format("exec CancelCake '{0}'", cakeBillNo);
                    cmd.CommandType = CommandType.Text;
                    result = cmd.ExecuteNonQuery();
                }
                return result;
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetAllLoginsByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT L.EmpID, E.EmpName, L.UserName, L.[Password] FROM Login L JOIN KSSalaries.dbo.Employees E ON L.EmpID = E.EmpID WHERE L.BranchID = '" + branch + "'", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int DeleteUser(string empID, string userName, string pwd, string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                int result = 0;

                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = string.Format("DELETE FROM Login WHERE EmpID = '{3}' AND UserName = '{0}' AND Password = '{1}' AND BranchID = '{2}'", userName, pwd, branch, empID);
                    cmd.CommandType = CommandType.Text;
                    result = cmd.ExecuteNonQuery();
                }
                return result;
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int AddUser(string empID, string userName, string pwd, string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                int result = 0;

                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format("SELECT 1 FROM Login WHERE EmpID = '{3}' AND UserName = '{0}' AND Password = '{1}' AND BranchID = '{2}'", userName, pwd, branch, empID);
                    object res = cmd.ExecuteScalar();
                    if (res != null && res.ToString() == "1")
                        return -1;

                    cmd.CommandText = string.Format("INSERT INTO Login (EmpID, UserName, Password, BranchID) VALUES ('{3}', '{0}', '{1}', '{2}')", userName, pwd, branch, empID);
                    result = cmd.ExecuteNonQuery();
                }
                return result;
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetAllCakeFlavoursByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);
                //else 
                if (branch == "0")
                    con = new SqlConnection(mainCon);
                //else if (branch == "-1")
                //    con = new SqlConnection(mainCSCCon);
                else if (branch == "-2")
                    con = new SqlConnection(mainSangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, Flavour, PricePerKG FROM CakeFlavours", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int AddOrUpdateCakeFlavour(string id, string flavourName, string flavourPrice, string branch)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "0")
                    con = new SqlConnection(mainCon);
                //else if (branch == "-1")
                //    con = new SqlConnection(mainCSCCon);
                else if (branch == "-2")
                    con = new SqlConnection(mainSangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CentralAddOrUpdateCakeFlavour {0}, '{1}', {2}", id, flavourName, flavourPrice), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int SyncCakeFlavoursByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                int branchInt = int.Parse(branch);
                //if (branchInt < 6 && branchInt > 0)
                //    con = new SqlConnection(mainCon);
                //else if (branchInt > 5 && branchInt < 9)
                //    con = new SqlConnection(mainCSCCon);
                if (branchInt == 6)
                    con = new SqlConnection(mainSangareddyCon);
                else
                    con = new SqlConnection(mainCon);

                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, Flavour, PricePerKG FROM CakeFlavours", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateCakeFlavour {0}, '{1}', {2}", row[0].ToString(), row[1].ToString(), row[2].ToString());
                    cmdChild.CommandType = CommandType.Text;
                    cmdChild.Connection = con;
                    int res = cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetAllCakeShapesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, CakeShape FROM CakeShapes", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int AddCakeShape(string id, string shape)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CentralAddCakeShape {0}, '{1}'", id, shape), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int SyncCakeShapesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, CakeShape FROM CakeShapes", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateCakeShape {0}, '{1}'", row[0].ToString(), row[1].ToString());
                    cmdChild.CommandType = CommandType.Text;
                    cmdChild.Connection = con;
                    int res = cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetAllExpenseItemsByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);
                //else 
                if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, ExpenseItem FROM MasterExpenses", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int AddExpenseItem(string id, string expenseItem)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CentralAddExpenseItem {0}, '{1}'", id, expenseItem), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int SyncExpenseItemsByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, ExpenseItem FROM MasterExpenses", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateExpenseItem {0}, '{1}'", row[0].ToString(), row[1].ToString());
                    cmdChild.CommandType = CommandType.Text;
                    cmdChild.Connection = con;
                    int res = cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetAllCompaniesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, CreditNoteCompany FROM MasterCreditNotes", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int AddCreditNoteCompany(string id, string creditNoteCompany)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CentralAddCreditNoteCompany {0}, '{1}'", id, creditNoteCompany), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int SyncCreditNoteCompaniesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, CreditNoteCompany FROM MasterCreditNotes", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();
                cmdChild.CommandType = CommandType.Text;
                cmdChild.Connection = con;

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateCreditNoteCompany {0}, '{1}'", row[0].ToString(), row[1].ToString());
                    int res = cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetCreditNoteRatesByBranchAndCompany(string branch, string company)
        {
            SqlConnection con = null;

            try
            {
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);
                //else 
                if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT ItemCode, ItemName, 0 AS ID, 0.0 AS SellingPrice FROM MasterItems;SELECT ID, ItemCode, SellingPrice FROM CreditNoteRates WHERE CreditNoteCompanyID = {0}", company), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables.Count == 2)
                        {
                            DataTable dtItems = ds.Tables[0];
                            foreach (DataRow row in ds.Tables[1].Rows)
                            {
                                DataRow dr = dtItems.Select("ItemCode = '" + row[1].ToString() + "'").FirstOrDefault();
                                if (dr != null)
                                {
                                    dr[2] = row[0];
                                    dr[3] = row[2];
                                }
                            }
                            return dtItems;
                        }
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int UpdateCreditNoteRates(string company, List<CompanyRateItem> items)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;
            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                //Delete Rates By Company ID
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.Transaction = trans = con.BeginTransaction();

                cmd.CommandText = string.Format("DELETE FROM CreditNoteRates WHERE CreditNoteCompanyID = {0}", company);
                cmd.ExecuteNonQuery();

                foreach (CompanyRateItem item in items)
                {
                    cmd.CommandText = string.Format("INSERT INTO [CreditNoteRates] ([CreditNoteCompanyID], [ItemCode], [SellingPrice]) VALUES ({0}, {1}, {2})", company, item.ItemCode, item.Price);
                    cmd.ExecuteNonQuery();
                }

                trans.Commit();
                return 1;
            }
            catch (Exception)
            {
                trans.Rollback();
                return 0;
            }
            finally
            {
                con.Close();
            }
        }

        internal static int SyncCreditNoteRatesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT CreditNoteCompanyID, ItemCode, SellingPrice FROM CreditNoteRates", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();
                cmdChild.Connection = con;
                cmdChild.CommandType = CommandType.Text;

                cmdChild.CommandText = "DELETE FROM CreditNoteRates";
                cmdChild.ExecuteNonQuery();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("INSERT INTO [CreditNoteRates] ([CreditNoteCompanyID], [ItemCode], [SellingPrice]) VALUES ({0}, {1}, {2})", row[0].ToString(), row[1].ToString(), row[2].ToString());
                    cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetVendorRatesByBranchAndCompany(string branch, string vendor)
        {
            SqlConnection con = null;

            try
            {
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);
                //else 
                if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT ItemCode, ItemName, 0 AS ID, 0.0 AS SellingPrice, SellingPrice AS ActualPrice FROM MasterItems;SELECT ID, ItemCode, SellingPrice FROM VendorRates WHERE VendorID = {0}", vendor), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables.Count == 2)
                        {
                            DataTable dtItems = ds.Tables[0];
                            foreach (DataRow row in ds.Tables[1].Rows)
                            {
                                DataRow dr = dtItems.Select("ItemCode = '" + row[1].ToString() + "'").FirstOrDefault();
                                if (dr != null)
                                {
                                    dr[2] = row[0];
                                    dr[3] = row[2];
                                }
                            }
                            return dtItems;
                        }
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static DataTable GetAllVendorsByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, Vendor, IsValid FROM MasterVendors WHERE IsValid = 1", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int AddOrUpdateVendor(string id, string vendor, string isValid)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CentralAddVendor {0}, '{1}', {2}", id, vendor, isValid), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int SyncVendorsByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, Vendor FROM MasterVendors", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();
                cmdChild.CommandType = CommandType.Text;
                cmdChild.Connection = con;

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateVendor {0}, '{1}'", row[0].ToString(), row[1].ToString());
                    int res = cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int UpdateVendorRates(string vendor, List<VendorItem> items)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;
            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();


                //Delete Rates By Company ID
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.Transaction = trans = con.BeginTransaction();

                cmd.CommandText = string.Format("DELETE FROM VendorRates WHERE VendorID = {0}", vendor);
                cmd.ExecuteNonQuery();

                foreach (VendorItem item in items)
                {
                    cmd.CommandText = string.Format("INSERT INTO [VendorRates] ([VendorID], [ItemCode], [SellingPrice]) VALUES ({0}, {1}, {2})", vendor, item.ItemCode, item.Price);
                    cmd.ExecuteNonQuery();
                }

                trans.Commit();
                return 1;
            }
            catch (Exception)
            {
                trans.Rollback();
                return 0;
            }
            finally
            {
                con.Close();
            }
        }

        internal static int SyncVendorRatesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);
                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT VendorID, ItemCode, SellingPrice FROM VendorRates", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();
                cmdChild.Connection = con;
                cmdChild.CommandType = CommandType.Text;

                cmdChild.CommandText = "DELETE FROM VendorRates";
                cmdChild.ExecuteNonQuery();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("INSERT INTO [VendorRates] ([VendorID], [ItemCode], [SellingPrice]) VALUES ({0}, {1}, {2})", row[0].ToString(), row[1].ToString(), row[2].ToString());
                    cmdChild.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int UpdateBranchItem(string branch, string itemCode, int group)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE [MasterItems] SET [GroupID] = {1} WHERE [ItemCode] = {0}", itemCode, group), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static int SyncMasterItems(string branch)
        {
            SqlConnection con = null;

            try
            {
                int branchInt = int.Parse(branch);

                //if (branchInt > 0 && branchInt < 6)
                //    con = new SqlConnection(mainCon);
                //else if (branchInt > 5 && branchInt < 9)
                //    con = new SqlConnection(mainCSCCon);
                //else if (branchInt > 8 && branchInt < 10)
                //    con = new SqlConnection(mainSangareddyCon);

                if (branchInt == 6)
                    con = new SqlConnection(mainSangareddyCon);
                else
                    con = new SqlConnection(mainCon);

                DataSet ds = new DataSet();

                if (con.State != ConnectionState.Open)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ItemCode, ItemName, CostPrice, SellingPrice, Dept, AcceptNegativeStock, AcceptDecimalStock, PopupLimit, IsLessStockShown, 0 AS StockAvailable, GroupID FROM MasterItems", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(ds);
                    }
                }

                con.Close();

                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);

                if (con.State != ConnectionState.Open)
                    con.Open();

                SqlCommand cmdChild = new SqlCommand();
                cmdChild.CommandType = CommandType.Text;
                cmdChild.Connection = con;

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    cmdChild.CommandText = string.Format("exec AddOrUpdateMasterItem '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}'", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString(), row[10].ToString());
                    int res = cmdChild.ExecuteNonQuery();
                }
                return 0;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        internal static DataTable GetAllSuppliers()
        {
            try
            {
                SqlConnection con = new SqlConnection(financialCon);
                using (SqlCommand cmd = new SqlCommand("SELECT SupplierID AS ID, SupplierName FROM MasterSuppliers", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
            }
            return null;
        }

        internal static int AddSupplier(string supplier)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO [MasterSuppliers] ([SupplierName]) VALUES ('{0}')", supplier), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetBulkOrders(string branch, DateTime date, bool forWholeMonth, bool isConnectToBranch)
        {
            SqlConnection con = null;

            try
            {
                string str;

                if (forWholeMonth)
                {
                    str = string.Format("SELECT BillNo, PhoneNo, TotalAmount, CONVERT(VARCHAR, BookedDate, 106) AS BookedDate, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, IsOrderCancelled, CancelledReason, [USER] AS Cashier FROM BulkOrder WHERE Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2}", branch, date.Month, date.Year);
                }
                else
                {
                    str = string.Format("SELECT BillNo, PhoneNo, TotalAmount, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, IsOrderCancelled, CancelledReason, [USER] AS Cashier FROM BulkOrder WHERE DATEDIFF(DD, BookedDate, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch);
                }

                if (isConnectToBranch)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    if (branch == "0" || branch == "-1" || branch == "-2")
                        con = new SqlConnection(mainCon);
                }

                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch { }
            return null;
        }

        internal static DataTable GetBillDetailsByBillNo(string billNo, string branch)
        {
            SqlConnection con = null;

            try
            {
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCon);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCon);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCon);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCon);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "6")
                //    con = new SqlConnection(sangareddyCon);
                //else if (branch == "0" || branch == "-1" || branch == "-2")
                con = new SqlConnection(mainCon);

                using (SqlCommand cmd = new SqlCommand("SELECT BillNo, Item AS ItemCode, MI.ItemName, BD.Qty FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE BillNo = '" + billNo + "'", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static int CancelBulkOrder(string billNo, string branch, bool isConnectToBranch)
        {
            SqlConnection con = null;

            try
            {
                if (isConnectToBranch)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCon);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCon);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCon);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCon);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    else if (branch == "6")
                        con = new SqlConnection(sangareddyCon);
                    else if (branch == "0" || branch == "-1" || branch == "-2")
                        con = new SqlConnection(mainCon);
                }
                else
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE BulkOrder SET IsOrderCancelled = 1 WHERE BillNo = '{0}'", billNo), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetVendorBills(string branch, DateTime date, bool forWholeMonth, bool isConnectToBranch)
        {
            SqlConnection con = null;
            try
            {
                string str;

                if (isConnectToBranch)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (forWholeMonth)
                {
                    str = string.Format("SELECT MV.Vendor, VS.BillNo, VS.Amount, CONVERT(VARCHAR, VS.Date, 106) AS Date, B.IsBillCancelled AS IsCancelled FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND MONTH(VS.Date) = {1} AND YEAR(VS.Date) = {2};", branch, date.Month, date.Year);
                }
                else
                {
                    str = string.Format("SELECT MV.Vendor, VS.BillNo, VS.Amount, CONVERT(VARCHAR, VS.Date, 106) AS Date, B.IsBillCancelled AS IsCancelled FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND DateDiff(dd, VS.Date, '{1}') = 0;", branch, date.ToString("MM/dd/yyyy"));
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = str;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }

            return null;
        }

        internal static int CancelVendorBill(string billNo, string branch, bool isConnectToBranch)
        {
            SqlConnection con = null;

            try
            {
                if (isConnectToBranch)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCon);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE Bill SET IsBillCancelled = 1 WHERE BillNo = '{0}'", billNo), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static object GetCreditNotes(string branch, DateTime date, bool forWholeMonth, bool isConnectToBranch)
        {
            SqlConnection con = null;
            try
            {
                string str;

                if (isConnectToBranch)
                {
                    //if (branch == "1")
                    //    con = new SqlConnection(chandanagarCon);
                    //else if (branch == "2")
                    //    con = new SqlConnection(miyapurCon);
                    //else if (branch == "3")
                    //    con = new SqlConnection(kukatpallyCon);
                    //else if (branch == "4")
                    //    con = new SqlConnection(ameerpetCon);
                    //else if (branch == "5")
                    //    con = new SqlConnection(jntuCon);
                    //else if (branch == "6")
                    //    con = new SqlConnection(csc1Con);
                    //else if (branch == "7")
                    //    con = new SqlConnection(csc2Con);
                    //else if (branch == "8")
                    //    con = new SqlConnection(csc3Con);
                    //else if (branch == "9")
                    //    con = new SqlConnection(sangareddyCon);
                }
                else
                    con = new SqlConnection(mainCon);

                if (forWholeMonth)
                {
                    str = string.Format("SELECT MCN.CreditNoteCompany, CN.BillNo, CN.Amount, CONVERT(VARCHAR, CN.Date, 106) AS Date, B.IsBillCancelled AS IsCancelled FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON CN.BillNo = B.BillNo WHERE CN.Branch = {0} AND MONTH(CN.Date) = {1} AND YEAR(CN.Date) = {2};", branch, date.Month, date.Year);
                }
                else
                {
                    str = string.Format("SELECT MCN.CreditNoteCompany, CN.BillNo, CN.Amount, B.IsBillCancelled AS IsCancelled FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON CN.BillNo = B.BillNo WHERE CN.Branch = {0} AND DateDiff(dd, CN.Date, '{1}') = 0", branch, date.ToString("MM/dd/yyyy"));
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = str;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }

            return null;
        }

        internal static DataTable GetSectionsFromStockManagement()
        {
            try
            {
                SqlConnection con = null;

                con = new SqlConnection(mainCon);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT SectionID, SectionName FROM Sections WITH (NOLOCK)"), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        if (ds != null && ds.Rows.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            { }
            return null;
        }

        internal static DataTable GetAllDepartmentsByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                else if (branch == "6")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT DeptID, DeptCode, DeptName, IsSlipRequired FROM Department WITH (NOLOCK)", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        if (ds != null && ds.Rows.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int AddOrUpdateDepartment(string deptID, string deptCode, string deptName, bool isSlipReq)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CentralAddDepartment {0}, '{1}', '{2}', {3}", deptID, deptCode, deptName, isSlipReq), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        adapter.Fill(ds);
                        if (ds != null && ds.Rows.Count > 0)
                            return int.Parse(ds.Rows[0][0].ToString());
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetCommonCodes()
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, ItemCodes FROM CommonCode WITH (NOLOCK)", con))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int AddOrUpdateCommonCode(string id, string codes)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec CentralAddUpdateCommonCode '{0}', '{1}'", id, codes), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetEmployeesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCon);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT EmpID, CAST(EmpID AS VARCHAR) + ' - ' + EmpName AS EmpName FROM KSSalaries.dbo.Employees WITH (NOLOCK) WHERE IsValid = 1 AND IsLeft = 0", branch), con))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static bool ValidateVersion(int appID, int version)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT ApplicationID FROM VersionManagement WHERE ApplicationID = {0} AND Version = {1}", appID, version), mainCon);
                adapter.Fill(dt);
                if (dt != null && dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception)
            { }
            return false;
        }
    }

    public class CompanyRateItem
    {
        public int ItemCode { get; set; }
        public double Price { get; set; }
    }

    public class VendorItem
    {
        public int ItemCode { get; set; }
        public double Price { get; set; }
    }

    public static class DBHelperSample
    {
        private static SqlConnection kukatpallyCS = new SqlConnection(ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ConnectionString);
        private static SqlConnection chandanagarCS = new SqlConnection(ConfigurationManager.ConnectionStrings["ChandanagarConnectionString"].ConnectionString);
        private static SqlConnection miyapurCS = new SqlConnection(ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ConnectionString);
        private static SqlConnection ameerpetCS = new SqlConnection(ConfigurationManager.ConnectionStrings["AmeerpetConnectionString"].ConnectionString);
        private static SqlConnection mainCS = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString);

        private static string mainCon = ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString;
        private static string kukatpallyCon = ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ConnectionString;
        private static string miyapurCon = ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ConnectionString;
        //private static string salariesCon = ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ConnectionString;

        private static string chandanagarCon = ConfigurationManager.ConnectionStrings["ChandanagarConnectionString"].ConnectionString;
        private static string ameerpetCon = ConfigurationManager.ConnectionStrings["AmeerpetConnectionString"].ConnectionString;
        private static string jntuCon = ConfigurationManager.ConnectionStrings["JNTUConnectionString"].ConnectionString;

        #region All

        internal static DataTable GetStockByBranchToUpdate(string branch, string item)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCon);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCon);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCon);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCon);
                else if (branch == "5")
                    con = new SqlConnection(jntuCon);
                //else if (branch == "6")
                //    con = new SqlConnection(san

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT ItemCode AS Code, ItemName, AcceptDecimalStock, StockAvailable, '' AS StockAdding FROM MasterItems WHERE ItemCode like '{0}%' OR ItemName like '{0}%' ORDER BY CONVERT(BIGINT, ItemCode)", item), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return null;
        }

        internal static int UpdateStockByBranch(DataTable dt, string branch)
        {
            SqlConnection con = null;

            if (branch == "1")
                con = new SqlConnection(chandanagarCon);
            else if (branch == "2")
                con = new SqlConnection(miyapurCon);
            else if (branch == "3")
                con = new SqlConnection(kukatpallyCon);
            else if (branch == "4")
                con = new SqlConnection(ameerpetCon);
            else if (branch == "5")
                con = new SqlConnection(jntuCon);

            if (con.State == ConnectionState.Closed)
                con.Open();

            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                SqlCommand cmd = new SqlCommand();

                foreach (DataRow row in dt.Rows)
                {
                    if (row[4].ToString() != string.Empty && row[4].ToString() != "0")
                    {
                        cmd.CommandText = string.Format("exec AddStockByItemCode {0}, {1}, 'Manager'", row[0].ToString(), row[4].ToString());
                        cmd.Connection = con;
                        cmd.Transaction = trans;
                        cmd.ExecuteScalar();
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        internal static DataTable GetItemsByBranch(string branch, string item)
        {
            try
            {
                SqlConnection con = null;

                if (branch == "Kukatpally")
                    con = kukatpallyCS;
                else if (branch == "Ameerpet")
                    con = ameerpetCS;
                else if (branch == "Chandanagar")
                    con = chandanagarCS;
                else if (branch == "Miyapur")
                    con = miyapurCS;

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT MI.ItemCode AS Code, MI.ItemName, MI.Dept, D.DeptName, MI.CostPrice, MI.SellingPrice, MI.AcceptNegativeStock, MI.AcceptDecimalStock, MI.PopupLimit, MI.IsLessStockShown, MI.GroupID FROM MasterItems MI JOIN Department D ON MI.Dept = D.DeptID WHERE MI.ItemCode like '{0}%' OR MI.ItemName like '{0}%'", item), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            return null;
        }

        internal static DataTable GetDepartments(string branch)
        {
            try
            {
                SqlConnection con = null;

                if (branch == "Kukatpally")
                    con = kukatpallyCS;
                else if (branch == "Ameerpet")
                    con = ameerpetCS;
                else if (branch == "Chandanagar")
                    con = chandanagarCS;
                else if (branch == "Miyapur")
                    con = miyapurCS;

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT DeptID, DeptCode + ' - ' + DeptName As Department FROM Department"), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            return null;
        }

        internal static int UpdateItem(string branch, string itemCode, string itemName, int department, double price, bool negativeStock, bool allowDecimal, double popupLimit, bool showInBilling, int group)
        {
            SqlConnection con = null;

            if (branch == "Kukatpally")
                con = kukatpallyCS;
            else if (branch == "Ameerpet")
                con = ameerpetCS;
            else if (branch == "Chandanagar")
                con = chandanagarCS;
            else if (branch == "Miyapur")
                con = miyapurCS;

            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddOrModifyItem '{0}', '{1}', {2}, {3}, {4}, {5}, {6}, {7}, {8}", itemCode, itemName, price, department, negativeStock, allowDecimal, popupLimit, showInBilling, group), con, trans))
                {
                    string result = cmd.ExecuteNonQuery().ToString();
                    if (result != null && result != string.Empty)
                    {
                        trans.Commit();
                        return Int32.Parse(result);
                    }
                }
                trans.Commit();
                return 0;
            }
            catch (Exception)
            {
                trans.Rollback();
                return -1;
            }
            finally
            {
                con.Close();
            }
        }

        #endregion

        #region Kukatpally Branch Level

        internal static DataSet GetFinancialReport(DateTime dateTime, string branchID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN Bill B ON B.BillNo = BD.BillNo WHERE DateDiff(dd, BD.Date, '{0}') = 0  AND B.IsBillCancelled = 0 AND BD.Branch = {1} GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode; SELECT ROUND(SUM(TotalAmount),2) AS TotalSales, ROUND(SUM(SodexhoPayment),2) AS SodexhoSales, ROUND(SUM(CardPayment),2) AS CardSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS Discount, ROUND(SUM(CashPayment),2) AS AvailableCash FROM Bill WHERE DateDiff(dd, Bill.BillDate, '{0}') = 0 AND IsBillCancelled = 0 AND Branch = {1}", dateTime.ToString("MM/dd/yyyy"), branchID), kukatpallyCS))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            { }
            return null;
        }

        internal static DataSet GetMonthlyReport(DateTime dt, string branchID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN Bill B ON BD.BillNo = B.BillNo WHERE BD.Date >= '{0}' AND BD.Date <= '{1}'  AND B.IsBillCancelled = 0 AND BD.Branch = {2} GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode; SELECT SUM(TotalAmount) AS TotalSales, SUM(SodexhoPayment) AS SodexhoSales, SUM(CardPayment) AS CardSales,SUM(TotalAmount - TotalNetAmount) AS Discount, SUM(CashPayment) AS AvailableCash FROM Bill WHERE BillDate >= '{0}' AND BillDate <= '{1}' AND IsBillCancelled = 0 AND Branch = {2}", dt.Month.ToString() + "/1/" + dt.Year.ToString(), new DateTime(dt.Year, dt.Month, 1).AddMonths(1).AddDays(-1).ToString("MM/dd/yyyy"), branchID), kukatpallyCS))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            { }
            return null;
        }

        #endregion

        #region Central Level

        internal static DataTable GetBranches()
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT BranchID, BranchName FROM MasterBranches", mainCS);
                adapter.Fill(ds, "Branches");
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            return null;
        }

        internal static DataTable GetStockByBranch(string branch)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT A.ItemCode, M.ItemName, A.Qty AS Stock FROM AvailableStock A JOIN MasterItems M ON A.ItemCode = M.ItemCode WHERE A.Branch = {0} ORDER BY A.ItemCode", branch), mainCS))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            return null;
        }

        internal static DataTable GetExpenseByBranchAndDate(string branch, DateTime date, bool forWholeMonth)
        {

            return null;
            //SELECT E.Expense AS ID, ME.ExpenseItem, E.Description, E.Amount FROM Expense E JOIN MasterExpenses ME ON E.Expense = ME.ID WHERE E.Branch = 3 AND DATEDIFF(DAY, '02/20/2011', E.Date) = 0
        }

        #endregion
    }
}
